using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace DataPopulation
{
    class DBUtils
    {
        public DBUtils()
        {
        }

        static public DataTable GetStats(string connectionString)
        {
            #region SQL Query
            string sql = "DECLARE @Name varchar(100)\n";
            sql = sql + "DECLARE @Uid varchar(40)\n";
            sql = sql + "DECLARE @AssnCount int\n";
            sql = sql + "DECLARE @TaskCount int\n";
            sql = sql + "DECLARE @LinkCount int\n";
            sql = sql + "CREATE TABLE [dbo].[#PROJECT_STATS](\n";
            sql = sql + "	[PROJ_NAME] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\n";
            sql = sql + "	[PROJ_UID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\n";
            sql = sql + "	[ASSN_COUNT] [int] NULL,\n";
            sql = sql + "	[TASK_COUNT] [int] NULL,\n";
            sql = sql + "	[LINK_COUNT] [int] NULL,\n";
            sql = sql + "	[PWA_INSTANCE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL\n";
            sql = sql + ") ON [PRIMARY]\n";
            sql = sql + "DECLARE assn_cursor CURSOR\n";
            sql = sql + "FOR SELECT PROJ_NAME, PROJ_UID FROM MSP_PROJECTS\n";
            sql = sql + "ORDER BY PROJ_NAME\n";
            sql = sql + "OPEN assn_cursor\n";
            sql = sql + "FETCH NEXT FROM assn_cursor INTO @Name, @Uid\n";
            sql = sql + "WHILE @@FETCH_STATUS = 0\n";
            sql = sql + "BEGIN\n";
            sql = sql + "	SELECT @AssnCount = count(*) FROM MSP_ASSIGNMENTS WHERE PROJ_UID = @Uid\n";
            sql = sql + "	SELECT @TaskCount = count(*) FROM MSP_TASKS WHERE PROJ_UID = @Uid\n";
            sql = sql + "	SELECT @LinkCount = count(*) FROM MSP_LINKS WHERE PROJ_UID = @Uid\n";
            sql = sql + "	INSERT INTO [#PROJECT_STATS]\n";
            sql = sql + "      ([PROJ_NAME],[PROJ_UID],[ASSN_COUNT],[TASK_COUNT],[LINK_COUNT])\n";
            sql = sql + "      VALUES\n";
            sql = sql + "      (@Name, @Uid, @AssnCount, @TaskCount, @LinkCount)\n";
            sql = sql + "	FETCH NEXT FROM assn_cursor INTO @Name, @Uid\n";
            sql = sql + "END\n";
            sql = sql + "CLOSE assn_cursor\n";
            sql = sql + "DEALLOCATE assn_cursor\n";
            sql = sql + "SELECT [PROJ_NAME],[PROJ_UID],[ASSN_COUNT],[TASK_COUNT],[LINK_COUNT]\n";
            sql = sql + "FROM [#PROJECT_STATS]\n";
            sql = sql + "ORDER BY PROJ_NAME ASC\n";
            sql = sql + "DROP TABLE [#PROJECT_STATS]\n";
            #endregion

            DataSet dsStats = new DataSet();
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;
                cn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                string[] tables = new string[1];
                tables[0] = "PROJECT_STATS";
                dsStats.Load(reader, LoadOption.OverwriteChanges, tables);
                reader.Close();
            }
            return dsStats.Tables["PROJECT_STATS"];
        }

        static public DataTable CreateLinkTable()
        {
            DataTable dtLinks = new DataTable("MSP_LINKS");
            dtLinks.Columns.Add("LINK_UID", typeof(Guid));
            dtLinks.Columns.Add("PROJ_UID", typeof(Guid));
            dtLinks.Columns.Add("RESERVED_DATA", typeof(string));
            dtLinks.Columns.Add("LINK_IS_CROSS_PROJ", typeof(bool));
            dtLinks.Columns.Add("LINK_PRED_UID", typeof(Guid));
            dtLinks.Columns.Add("LINK_SUCC_UID", typeof(Guid));
            dtLinks.Columns.Add("LINK_TYPE", typeof(short));
            dtLinks.Columns.Add("LINK_LAG_FMT", typeof(short));
            dtLinks.Columns.Add("LINK_LAG", typeof(int));
            dtLinks.Columns.Add("LINK_BINARY_DATA", typeof(byte[]));
            dtLinks.Columns.Add("CREATED_DATE", typeof(DateTime));
            dtLinks.Columns.Add("MOD_DATE", typeof(DateTime));
            dtLinks.Columns.Add("CREATED_REV_COUNTER", typeof(int));
            dtLinks.Columns.Add("MOD_REV_COUNTER", typeof(int));
            return dtLinks;
        }

        static public void InsertLinks(string connectionString, DataTable dtLinks)
        {
            #region SQL Query
            string sql = @"INSERT INTO [dbo].[MSP_LINKS] ([LINK_UID], [PROJ_UID], [RESERVED_DATA], ";
            sql = sql + "[LINK_IS_CROSS_PROJ], [LINK_PRED_UID], [LINK_SUCC_UID], [LINK_TYPE], ";
            sql = sql + "[LINK_LAG_FMT], [LINK_LAG], [LINK_BINARY_DATA], [CREATED_DATE], [MOD_DATE], ";
            sql = sql + "[CREATED_REV_COUNTER], [MOD_REV_COUNTER]) VALUES (@LINK_UID, @PROJ_UID, ";
            sql = sql + "@RESERVED_DATA, @LINK_IS_CROSS_PROJ, @LINK_PRED_UID, @LINK_SUCC_UID, @LINK_TYPE, ";
            sql = sql + "@LINK_LAG_FMT, @LINK_LAG, @LINK_BINARY_DATA, @CREATED_DATE, @MOD_DATE, ";
            sql = sql + "@CREATED_REV_COUNTER, @MOD_REV_COUNTER);";
            #endregion

            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;

                #region Create Parameters
                cmd.Parameters.Add(new SqlParameter("@LINK_UID", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, 0, 0, "LINK_UID", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@PROJ_UID", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, 0, 0, "PROJ_UID", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@RESERVED_DATA", SqlDbType.Char, 0, ParameterDirection.Input, 0, 0, "RESERVED_DATA", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_IS_CROSS_PROJ", SqlDbType.Bit, 0, ParameterDirection.Input, 0, 0, "LINK_IS_CROSS_PROJ", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_PRED_UID", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, 0, 0, "LINK_PRED_UID", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_SUCC_UID", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, 0, 0, "LINK_SUCC_UID", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_TYPE", SqlDbType.SmallInt, 0, ParameterDirection.Input, 0, 0, "LINK_TYPE", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_LAG_FMT", SqlDbType.SmallInt, 0, ParameterDirection.Input, 0, 0, "LINK_LAG_FMT", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_LAG", SqlDbType.Int, 0, ParameterDirection.Input, 0, 0, "LINK_LAG", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@LINK_BINARY_DATA", SqlDbType.Image, 0, ParameterDirection.Input, 0, 0, "LINK_BINARY_DATA", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@CREATED_DATE", SqlDbType.DateTime, 0, ParameterDirection.Input, 0, 0, "CREATED_DATE", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@MOD_DATE", SqlDbType.DateTime, 0, ParameterDirection.Input, 0, 0, "MOD_DATE", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@CREATED_REV_COUNTER", SqlDbType.Int, 0, ParameterDirection.Input, 0, 0, "CREATED_REV_COUNTER", DataRowVersion.Current, false, null, "", "", ""));
                cmd.Parameters.Add(new SqlParameter("@MOD_REV_COUNTER", SqlDbType.Int, 0, ParameterDirection.Input, 0, 0, "MOD_REV_COUNTER", DataRowVersion.Current, false, null, "", "", ""));
                #endregion

                cn.Open();

                foreach (DataRow dr in dtLinks.Rows)
                {
                    cmd.Parameters["@LINK_UID"].Value = dr["LINK_UID"];
                    cmd.Parameters["@PROJ_UID"].Value = dr["PROJ_UID"];
                    cmd.Parameters["@RESERVED_DATA"].Value = dr["RESERVED_DATA"];
                    cmd.Parameters["@LINK_IS_CROSS_PROJ"].Value = dr["LINK_IS_CROSS_PROJ"];
                    cmd.Parameters["@LINK_PRED_UID"].Value = dr["LINK_PRED_UID"];
                    cmd.Parameters["@LINK_SUCC_UID"].Value = dr["LINK_SUCC_UID"];
                    cmd.Parameters["@LINK_TYPE"].Value = dr["LINK_TYPE"];
                    cmd.Parameters["@LINK_LAG_FMT"].Value = dr["LINK_LAG_FMT"];
                    cmd.Parameters["@LINK_LAG"].Value = dr["LINK_LAG"];
                    cmd.Parameters["@LINK_BINARY_DATA"].Value = dr["LINK_BINARY_DATA"];
                    cmd.Parameters["@CREATED_DATE"].Value = dr["CREATED_DATE"];
                    cmd.Parameters["@MOD_DATE"].Value = dr["MOD_DATE"];
                    cmd.Parameters["@CREATED_REV_COUNTER"].Value = dr["CREATED_REV_COUNTER"];
                    cmd.Parameters["@MOD_REV_COUNTER"].Value = dr["MOD_REV_COUNTER"];
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static void HackAddDependencytoDB(Guid projGuid, Guid predGuid, Guid succGuid, int seq, int predIndex, int succIndex, DataTable dtLinks)
        {
            byte[] binData = new byte[34];
            byte[] byteSeq = new byte[2];
            byte[] bytePred = new byte[2];
            byte[] byteSucc = new byte[2];
            byte[] byteJunk = new byte[2];
            for (int index = 0; index < 34; index++) binData[index] = 0;
            byteSeq = BitConverter.GetBytes(seq);
            bytePred = BitConverter.GetBytes(predIndex);
            byteSucc = BitConverter.GetBytes(succIndex);
            byteJunk = BitConverter.GetBytes(0x07F3);

            binData[0] = byteSeq[0];
            binData[1] = byteSeq[1];
            binData[4] = bytePred[0];
            binData[5] = bytePred[1];
            binData[8] = byteSucc[0];
            binData[9] = byteSucc[1];
            binData[32] = byteJunk[0];
            binData[33] = byteJunk[1];

            DataRow row = dtLinks.NewRow();
            row["LINK_UID"] = Guid.NewGuid();
            row["PROJ_UID"] = projGuid;
            row["RESERVED_DATA"] = null;
            row["LINK_IS_CROSS_PROJ"] = false;
            row["LINK_PRED_UID"] = predGuid;
            row["LINK_SUCC_UID"] = succGuid;
            row["LINK_TYPE"] = 1;
            row["LINK_LAG_FMT"] = 7;
            row["LINK_LAG"] = 0;
            row["LINK_BINARY_DATA"] = binData;
            row["CREATED_DATE"] = DateTime.Now;
            row["MOD_DATE"] = row["CREATED_DATE"];
            row["CREATED_REV_COUNTER"] = 0;
            row["MOD_REV_COUNTER"] = 0;
            dtLinks.Rows.Add(row);
        }
    }
}
